/**
 * 
 */
package com.teach.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.teach.entity.User;
import com.teach.util.JDBCutils;

/**
 * @author Liulianqiu
 *2020年7月18日
 */
public class UserDAO {
	Connection conn = null;
	PreparedStatement ps = null;
	ResultSet rs = null;
	boolean flag = false;

	//	增加用户
		public boolean add(User user) {
			try {
			//获取连接
			conn = JDBCutils.getConnection();
					
			//预编译sql语句，获得PreparedStatement对象
					String sql = "insert into liulianqiu values(null,?,?,?,?)";
					ps = conn.prepareStatement(sql);
					
			//填充占位符
					ps.setObject(1, user.getLlq_name());
					ps.setInt(2, user.getLlq_age());
					ps.setDate(3, user.getLlq_birthday());
					ps.setObject(4, user.getLlq_sex());
					
			//执行sql语句
					int i = ps.executeUpdate();
					if(i == 1) {
						flag = true;
					}
			} catch (Exception e) {
				e.printStackTrace();
			}finally {
		 //关闭资源
				JDBCutils.closeConnection(conn, ps);
			  }
			 return flag;
		}
		
//	     修改用户信息
		public boolean updateById(User user,int id) {
			try {
				conn = JDBCutils.getConnection();
				String sql = "update liulianqiu set llq_name=?,llq_age=?,llq_birthday=?,llq_sex=? where llq_id=?";
				ps = conn.prepareStatement(sql);
				ps.setObject(1, user.getLlq_name());
				ps.setObject(2, user.getLlq_age());
				ps.setObject(3, user.getLlq_birthday());
				ps.setObject(4, user.getLlq_sex());
				ps.setObject(5, id);
				
				if(ps.executeUpdate()==1) {
					flag = true;
				}
			} catch (Exception e) {
				e.printStackTrace();
			}finally {
				JDBCutils.closeConnection(conn, ps);
			}
			return flag;
		}
		
//		删除用户
		public boolean deleteById(int id) {
			try {
				conn = JDBCutils.getConnection();
				String sql = "delete from liulianqiu where llq_id=?";
				ps = conn.prepareStatement(sql);
				ps.setObject(1, id);
				if(ps.executeUpdate()==1) {
					flag = true;
				}
			}catch (Exception e) {
				e.printStackTrace();
			}finally {
				JDBCutils.closeConnection(conn, ps);
			}
			return flag;
		}
		
//		查询单个用户
		public User selectById(int id) throws SQLException {
			User user = null;
			try {
				conn = JDBCutils.getConnection();
				String sql = "select * from liulianqiu where llq_id=?";
				ps = conn.prepareStatement(sql);
				ps.setObject(1, id);
				rs = ps.executeQuery();
				if(rs.next()) {
					User user1 = new User();
					user1.setLlq_id(rs.getInt("llq_id"));
					user1.setLlq_name(rs.getString("llq_name"));
					user1.setLlq_age(rs.getInt("llq_age"));
					user1.setLlq_birthday(rs.getDate("llq_birthday"));
					user1.setLlq_sex(rs.getString("llq_sex"));
					user = user1;
				}else {
					throw new SQLException("没有发现该用户的记录");
				}
			  }catch (Exception e) {
				e.printStackTrace();
			}finally {
			   JDBCutils.closeResources(conn, ps, rs);
			}
			return user;
		}
		
//		查询部分用户
		public List<User> selectAll(int index,int len) {
			List<User> list = new ArrayList<User>();
			try {
				conn = JDBCutils.getConnection();
				String sql = "select * from liulianqiu order by llq_id asc limit ?,?";
				ps = conn.prepareStatement(sql);
				ps.setObject(1, index);
				ps.setObject(2, len);
				rs = ps.executeQuery();
				while(rs.next()) {
					User user1 = new User();
					user1.setLlq_id(rs.getInt("llq_id"));
					user1.setLlq_name(rs.getString("llq_name"));
					user1.setLlq_age(rs.getInt("llq_age"));
					user1.setLlq_birthday(rs.getDate("llq_birthday"));
					user1.setLlq_sex(rs.getString("llq_sex"));
					list.add(user1);
				}
			}catch (Exception e) {
				e.printStackTrace();
			}finally {
				JDBCutils.closeResources(conn, ps, rs);
			}
			return list;
		}
		
//		返回用户数量
		public int countAll() {
			int count = 0;
			try {
				conn = JDBCutils.getConnection();
				String sql = "select count(*) as c from liulianqiu";//进行记录查询计数
				ps = conn.prepareStatement(sql);
				rs = ps.executeQuery();
				while(rs.next()) {
					count = rs.getInt("c");
				}
			}catch (Exception e) {
				e.printStackTrace();
			}finally {
				JDBCutils.closeResources(conn, ps, rs);
			}
			return count;
		}
		
		//模糊查询
		public List<User> selectLike(String keyWord) {
			List<User> list = new ArrayList<User>();
			try {
				conn = JDBCutils.getConnection();
				StringBuffer sql = new StringBuffer( "select * from liulianqiu where llq_name like");
				sql.append(" '%"+keyWord+"%'");
				System.out.println(sql.toString());
				ps = conn.prepareStatement(sql.toString());
				rs = ps.executeQuery();
				while(rs.next()) {
					User user1 = new User();
					user1.setLlq_id(rs.getInt("llq_id"));
					user1.setLlq_name(rs.getString("llq_name"));
					user1.setLlq_age(rs.getInt("llq_age"));
					user1.setLlq_birthday(rs.getDate("llq_birthday"));
					user1.setLlq_sex(rs.getString("llq_sex"));
					list.add(user1);
				}
			}catch (Exception e) {
				e.printStackTrace();
			}finally {
				JDBCutils.closeResources(conn, ps, rs);
			}
			return list;
		}
		
		
		public User selectByUserName(String UserName) {
				//获取连接
				Connection conn = null;
				PreparedStatement prep = null;
				//执行sql语句，并获取结果集
				ResultSet rs = null;
				//生成一个User对象
				User user = null;
				try {
					conn = JDBCutils.getConnection();
					//预编译sql语句，并获取PreparedStatement对象
					String sql = "select * from liulianqiu where llq_name=?";
					prep = conn.prepareStatement(sql);
					//填充占位符
					prep.setString(1, UserName);
					rs = prep.executeQuery();
					if(rs.next()) { //一定要记得写这个，否则会报 Before start of result set 异常错误
						user = new User();
						user.setLlq_id(rs.getInt("llq_id"));
						user.setLlq_name(rs.getString("llq_name"));
						user.setLlq_age(rs.getInt("llq_age"));
						user.setLlq_birthday(rs.getDate("llq_birthday"));
						user.setLlq_sex(rs.getString("llq_sex"));
					}
				}catch (Exception e) {
					e.printStackTrace();
				}finally {
					//关闭资源
					JDBCutils.closeResources(conn, prep, rs);
				}
				return user;
			}
}
